Import or export data will never be an outdated topic for programmers. Data problems never lack in forums, blogs and programming websites. Different data import or export on various platforms always gets people into trouble. I have talked about how to import data from datatable to Excel spreadsheet for .NET. Now, I want to introduce how to import data from datatable to Excel Worksheet for WPF.
Please make sure that Spire.XLS for WPF and Visual Studio are correctly installed on system.Spire.XLS for WPF is a professional Excel component which supports to operate Excel 97-2003, Excel 2007 and Excel 2010. However, .NET Framework and Visual Studio must be installed for using Spire.XLS for WPF.
How to import data from datatable to Excel worksheet for WPF
At the first sight, you may find that the code of import data for .NET and for WPF is almost the same. But you may have a presentative judgement that it should be different, now please find the difference in the below procedure.
Please make sure that Spire.XLS for WPF and Visual Studio are correctly installed on system.Spire.XLS for WPF is a professional Excel component which supports to operate Excel 97-2003, Excel 2007 and Excel 2010. However, .NET Framework and Visual Studio must be installed for using Spire.XLS for WPF.
How to import data from datatable to Excel worksheet for WPF
At the first sight, you may find that the code of import data for .NET and for WPF is almost the same. But you may have a presentative judgement that it should be different, now please find the difference in the below procedure.
Step1. Create a new project
- Create a new project in by choosing WPF Application in Visual Studio.
- Create a new project by choosing WPF Application in Visual Studio.
- Set the Target framework property of this project in Solution Explorer to be .NET Framework 4.
- Add a button and dataGrid in MainWindow. The default button name is “Button1”.
Step2. Add reference and project namespaces.
- Add Spire.XLS. Wpf.dll as reference in Project.
- Add below namespaces at the top of the method.
using Spire.Xls;
using System.Data;
VB
Imports Spire.Xls
Imports System.Data
Step3. Import data from datatable to excel worksheet.
- Load an excel file from system to get data source
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\e-iceblue\Spire.XLS\Demos\Data\DatatableSample.xls");
//Initialize worksheet
Worksheet sheet = workbook.Worksheets[0];
DataTable table = sheet.ExportDataTable();
DataView view = new DataView(table);
this.dataGrid1.ItemsSource = view;
this.dataGrid1.AutoGenerateColumns = true;
}
VB
Private Sub MainWindow_Load(sender As Object, e As RoutedEventArgs)
Dim workbook As New Workbook()
workbook.LoadFromFile("D:\e-iceblue\Spire.XLS\Demos\Data\DatatableSample.xls")
'Initialize worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim table As DataTable = sheet.ExportDataTable()
Dim view As New DataView(table)
Me.dataGrid1.ItemsSource = view
Me.dataGrid1.AutoGenerateColumns = True
End Sub
2. Create a workbook and insert datatable
C#
Workbook workbook = new Workbook();
//Initialize worksheet
Worksheet sheet = workbook.Worksheets[0];
DataView view=(DataView)this.dataGrid1.ItemsSource;
DataTable table=view.Table;
sheet.InsertDataTable(table, true, 1, 1, -1, -1);
VB
Dim workbook As New Workbook()
'Initialize worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim view As DataView = DirectCast(Me.dataGrid1.ItemsSource, DataView)
Dim table As DataTable = view.Table
sheet.InsertDataTable(table, True, 1, 1, -1, -1)
3. Set table Style.C#
//Sets body style
CellStyle oddStyle = workbook.Styles.Add("oddStyle");
oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
oddStyle.KnownColor = ExcelColors.LightGreen1;
CellStyle evenStyle = workbook.Styles.Add("evenStyle");
evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
evenStyle.KnownColor = ExcelColors.LightTurquoise;
foreach (CellRange range in sheet.AllocatedRange.Rows)
{
if (range.Row % 2 == 0)
range.CellStyleName = evenStyle.Name;
else
range.CellStyleName = oddStyle.Name;
}
//Sets header style
CellStyle styleHeader = sheet.Rows[0].Style;
styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
styleHeader.VerticalAlignment = VerticalAlignType.Center;
styleHeader.KnownColor = ExcelColors.SeaGreen;
styleHeader.Font.KnownColor = ExcelColors.White;
styleHeader.Font.IsBold = true;
sheet.Columns[sheet.AllocatedRange.LastColumn - 1].Style.NumberFormat = "\"$\"#,##0";
sheet.Columns[sheet.AllocatedRange.LastColumn - 2].Style.NumberFormat = "\"$\"#,##0";
sheet.AllocatedRange.AutoFitColumns();
sheet.AllocatedRange.AutoFitRows();
sheet.Rows[0].RowHeight = 20;
VB
'Sets body style
Dim oddStyle As CellStyle = workbook.Styles.Add("oddStyle")
oddStyle.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin oddStyle.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
oddStyle.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin oddStyle.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
oddStyle.KnownColor = ExcelColors.LightGreen1
Dim evenStyle As CellStyle = workbook.Styles.Add("evenStyle") evenStyle.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
evenStyle.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin evenStyle.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin evenStyle.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
evenStyle.KnownColor = ExcelColors.LightTurquoise
For Each range As CellRange In sheet.AllocatedRange.Rows
If range.Row Mod 2 = 0 Then
range.CellStyleName = evenStyle.Name
Else
range.CellStyleName = oddStyle.Name
End If
Next
'Sets header style
Dim styleHeader As CellStyle = sheet.Rows(0).Style styleHeader.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
styleHeader.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
styleHeader.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
styleHeader.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
styleHeader.VerticalAlignment = VerticalAlignType.Center
styleHeader.KnownColor = ExcelColors.SeaGreen
styleHeader.Font.KnownColor = ExcelColors.White
styleHeader.Font.IsBold = True
sheet.Columns(sheet.AllocatedRange.LastColumn - 1).Style.NumberFormat = """$""#,##0"
sheet.Columns(sheet.AllocatedRange.LastColumn - 2).Style.NumberFormat = """$""#,##0"
sheet.AllocatedRange.AutoFitColumns()
sheet.AllocatedRange.AutoFitRows()
sheet.Rows(0).RowHeight = 20
Step4. Save and launch the project
C#
workbook.SaveToFile("sample.xls");
ExcelDocViewer(workbook.FileName);
VB
workbook.SaveToFile("sample.xls")
ExcelDocViewer(workbook.FileName)
Preview
More About Spire.XLS for WPF
Spire.XLS for WPF is a professional and powerful Excel component which enables developers/programmers to operate Excel files with their WPF applications.
To be a Reseller